﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.IO;

namespace TSCC.Common
{
    public class DAL
    {
        protected static DbProviderFactory factory = null;
        protected static DbConnection _connection = null;
        protected static DbConnection connection
        {
            get
            {
                if (_connection == null)
                {
                    
                    factory = DbProviderFactories.GetFactory(getDBProvider());
                    _connection = factory.CreateConnection(); 
                    _connection.ConnectionString = connectionString;
                }
                return _connection;
            }
        }

        public static DbCommand GetCommand()
        {
            return connection.CreateCommand();
        }


        protected static string getDBProvider()
        {
            string ConnectDB, mainConnection;

            ConnectDB = "ConnectDB";
            Configuration config = getConfiguration();            
            AppSettingsSection app = config.AppSettings;

            mainConnection = app.Settings[ConnectDB].Value;
            connectionString = config.ConnectionStrings.ConnectionStrings[mainConnection].ToString();
            if (mainConnection.Equals("Access"))
            {
                connectionString = string.Format(connectionString
                    , AppDomain.CurrentDomain.BaseDirectory);
            }            
            return config.ConnectionStrings.ConnectionStrings[mainConnection].ProviderName.ToString();
        }


        protected static Configuration getConfiguration()
        {
            string configFile = "Common.dll";
            Configuration config = null;
            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + configFile)) //如果這一層沒有，就再加bin
            {
                config = ConfigurationManager.OpenExeConfiguration(configFile);
            }
            else
            {
                config = ConfigurationManager.OpenExeConfiguration(
                    AppDomain.CurrentDomain.BaseDirectory + @"bin\" + configFile);
            }
            return config;
        }

        

        /// <summary>
        /// 取回連線字串，要注意的是字串名稱為
        /// </summary>
        /// 
        protected static String connectionString = "";


        /// <summary>
        /// 回傳 IDbDataParameter
        /// </summary>
        /// <param name="p_name">欄位名</param>
        /// <param name="p_dbType">欄位型態</param>
        /// <param name="p_value">值</param>
        /// <returns>IDbDataParameter</returns>
        public static DbParameter GetParameter(string p_name, DbType p_dbType, object p_value, DbParameter p_para)
        {
            p_para.ParameterName = p_name;
            p_para.DbType = p_dbType;
            p_para.Value = p_value;
            return p_para;
        }

        /// <summary>
        /// 要傳入的 Command 要先把Parameter 組好
        /// </summary>
        /// <param name="p_cmd">IDBCommand</param>
        /// <returns>int</returns>
        public static int ExecuteNoneQuery(DbCommand p_cmd)
        {
            int iResult = -1;
            try
            {
                //ConnectionOpenClose();
                connectionOpen();
                iResult = p_cmd.ExecuteNonQuery();

            }
            catch (DbException ex)
            {
                //TODO
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                //TODO
                exceptionHandle(ex);
            }
            finally
            {
                connection.Close();
            }
            return iResult;
        }

        /// <summary>
        /// 如果沒有打開，那就開了，如果已開了就不用開了^^
        /// </summary>
        protected static void connectionOpen()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        /// <summary>
        /// 如果關了就開，開了就關！
        /// </summary>
        //protected static void ConnectionOpenClose()
        //{
        //    if (Connection.State == ConnectionState.Closed)
        //    {
        //        Connection.Open();
        //    }
        //    else
        //    {
        //        Connection.Close();
        //    }            
        //}

        public static IDataReader GetDataReader(IDbCommand p_cmd)
        {
            //DbDataReader reader = null;
            IDataReader reader = null;
            try
            {
                //ConnectionOpenClose();
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                reader = p_cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {

            }
            return reader;
        }

        public static DataSet GetDataSet(DbCommand p_cmd)
        {

            DataSet ds = new DataSet();
            try
            {
                //ConnectionOpenClose();
                connectionOpen();
                DbDataAdapter da = getDataAdapter(p_cmd);
                da.Fill(ds);
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {
                //ConnectionOpenClose();
                connection.Close();
            }
            return ds;
        }

        //public static DataTable GetDataTable(DbCommand p_cmd)
        public static DataTable GetDataTable(IDbCommand p_cmd)
        {
            DataTable dt = new DataTable();
            try
            {
                dt.Load(GetDataReader(p_cmd));
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {

            }
            return dt;
        }

        /// <summary>
        /// 在這實體化 DataAdapter
        /// </summary>
        /// <returns></returns>
        protected static DbDataAdapter getDataAdapter(DbCommand p_cmd)
        {
            DbDataAdapter da = factory.CreateDataAdapter();
            da.SelectCommand = p_cmd;
            return da;
        }

        protected static void exceptionHandle(Exception ex)
        {
            //TODO 這未來要做什麼
            //Utilitys.ExceptionTOLog(ex);
        }
    }
}
